package DBLayer;
import ModelLayer.Currency;
import java.sql.*;
import java.util.ArrayList;

public class DBCurrency {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBCurrency() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Currency getCurrencyByID(int id) {
        return selectSingle("SELECT `id_currency`, `name`, `iso_code`, `sign`, `blank`, `format`, `decimals`, `conversion_rate`, `deleted` FROM `ps_currency` WHERE `id_currency` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Currency> getCurrencyList() {
        return selectMultiple("SELECT `id_currency`, `name`, `iso_code`, `sign`, `blank`, `format`, `decimals`, `conversion_rate`, `deleted` FROM `ps_currency` ORDER BY `id_currency` ASC;");
    }

    private Currency selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Currency currency = new Currency();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                currency = buildCurrency(result);
            }
            System.out.println("Fetching Currency Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return currency;
    }

    private ArrayList<Currency> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Currency> currencies = new ArrayList<Currency>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Currency currency = new Currency();
                currency = buildCurrency(result);
                currencies.add(currency);
            }
            System.out.println("Fetching Currency List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return currencies;
    }

    private Currency buildCurrency(ResultSet result) {
        Currency currency = new Currency();
        try {
            currency.setIDCurrency(result.getInt("id_currency"));
            currency.setName(result.getString("name"));
            currency.setIsoCode(result.getString("iso_code"));
            currency.setSign(result.getString("sign"));
            if (result.getInt("blank") == 1) {
                currency.setBlank(true);
            } else {
                currency.setBlank(false);
            }
            currency.setFormat(result.getInt("format"));
            if (result.getInt("decimals") == 1) {
                currency.setDecimals(true);
            } else {
                currency.setDecimals(false);
            }
            currency.setConversionRate(result.getFloat("conversion_rate"));
            if (result.getInt("deleted") == 1) {
                currency.setDeleted(true);
            } else {
                currency.setDeleted(false);
            }

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return currency;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addCurrency(Currency currency) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_currency` (`id_currency`, `name`, `iso_code`, `sign`, `blank`, `format`, `decimals`, `conversion_rate`, `deleted`) "
                + "VALUES (" + currency.getIDCurrency() + ", '" + currency.getName() + "', '" + currency.getIsoCode() + "', '" + currency.getSign() + "', ";
        if (currency.getBlank() == true)    { query += "1, "; }
        else                                { query += "0, "; }
        query += currency.getFormat() + ", " + currency.getDecimals() + ", " + currency.getConversionRate() + ", ";
        if (currency.getDeleted() == true)  { query += "1);"; }
        else                                { query += "0);"; }

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Currency ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateCurrency(Currency currency, int id) {
        int returnID = 0;
        Statement stmt = null;
        String query = "UPDATE `ps_currency` SET "
                + "`id_currency`        = "  + currency.getIDCurrency() + ", "
                + "`name`               = '" + currency.getName() + "', "
                + "`iso_code`           = '" + currency.getIsoCode() + "', "
                + "`sign`               = '" + currency.getSign() + "', ";
        if (currency.getBlank() == true)    { query += "`blank` = 1, "; }
        else                                { query += "`blank` = 0, "; }
        query += "`format`              = " + currency.getFormat() + ", ";
        if (currency.getDecimals() == true) { query += "`decimals` = 1, "; }
        else                                { query += "`decimals` = 0, "; }
        query += "`conversion_rate`     = " + currency.getConversionRate() + ", ";
        if (currency.getDeleted() == true)  { query += "`deleted` = 1 "; }
        else                                { query += "`deleted` = 0 "; }
        query  += "WHERE `id_currency` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = currency.getIDCurrency();
            System.out.println("Updated Currency ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteCurrency(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_currency` WHERE `id_currency` = " + id + " LIMIT 1;");
            System.out.println("Deleted Currency ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
